package com.DAO;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.Statement;

import com.beans.CommentForm;
import com.beans.NoticeForm;
import com.connection.ConnectionFactory;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

public class CommentDAO {

	private Connection con=null;
	public CommentDAO()
	{
		
		con = ConnectionFactory.getConnection();
		
	}
	
	public ArrayList<CommentForm> get(int postId) throws SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		ArrayList<CommentForm> list = new ArrayList<CommentForm>();
		try {
			String query = "select * from Comments where notice_Id=? order by id desc";
			stmt = con.prepareStatement(query);
			stmt.setInt(1,postId);
			rs = stmt.executeQuery();
			
			while(rs.next())
			{	
				CommentForm cf= new CommentForm();
				cf.setId(rs.getInt(1));
				cf.setDescription(rs.getString(2));
				cf.setCreatedById(rs.getString(3));
				//cf.setCreatorname(rs.getString(4));
				cf.setCreatedDate(rs.getDate(4));
				cf.setNoticeId(rs.getInt(5));
				list.add(cf);
			}
			
			System.out.println("list is....."+list);
			
			
			
		} catch (Exception e) {
			System.out.println("Error in insert comment......");
			e.printStackTrace();
		} finally {
			/* close connection */
			try {
				
				if (stmt != null) {
					stmt.close();
				}
				if (rs != null) {
					stmt.close();
				}

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	
	public int insert(CommentForm cf) throws SQLException {
		PreparedStatement stmt = null;
		int rowsadded = 0;
		try {
			
			String description = cf.getDescription();
			String createdbyid = cf.getCreatedById();
			//String creatorname=cf.getCreatorname();
			int noticeid = cf.getNoticeId();
			String query = "insert into comments(description,createdby_id,notice_id) values(?,?,?)";

			stmt = con.prepareStatement(query);
			stmt.setString(1,description);
			stmt.setString(2,createdbyid);
			stmt.setInt(3, noticeid);
			rowsadded = stmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			/* close connection */
			try {
				
				if (stmt != null) {
					stmt.close();
				}

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return rowsadded;
	}

	public int delete(int id) throws SQLException {
		int rowsdeleted = 0;
		PreparedStatement stmt = null;
		try {
			stmt = con.prepareStatement("delete from comments where id=?");
			stmt.setInt(1, id);
			rowsdeleted = stmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			/* close connection */
			try {
				
				if (stmt != null) {
					stmt.close();
				}

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return rowsdeleted;
	}
	
	
	
	public  ArrayList<CommentForm> setSize(int n) 
	{	
		ArrayList<CommentForm> list = new ArrayList<CommentForm>();
			String query = "select * from notice order by Notice_id desc limit 0,? ";
			PreparedStatement ps = null;
			ResultSet rs = null;
			try 
			{
				ps=con.prepareStatement(query);
				ps.setInt(1,n);
				rs = ps.executeQuery(query);
				CommentForm cf= new CommentForm();
				while(rs.next())
				{	
					cf.setId(rs.getInt(1));
					cf.setDescription(rs.getString(2));
					cf.setCreatedById(rs.getString(3));
					cf.setCreatedDate(rs.getDate(4));
					list.add(cf);
				}

			} catch (SQLException e)
			{
				e.printStackTrace();
			} finally 
			{
				/* close connection */
				try 
				{
					
					if (ps != null) 
					{
						ps.close();
					}
					if (ps != null) 
					{
						ps.close();
					}

				} catch (SQLException e) 
				{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		
				return list;			
			}
			
	}


	
	public void closeConnection()  throws SQLException
	{
		con.close();
		ConnectionFactory.closeConnection();
	}

}
